﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;


namespace NG
{
    class projectHandler : IProjectHandler
    {


        private string tableName = "[dbo].[Project]";
        private string tableName1 = "[dbo].[ProjectDepartments]";


        public void addProject(Project pro)
        {

            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;
            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }
            string sqlDateFormat1 = pro.oDate.ToString("yyy-MM-dd HH:mm");
            string sqlDateFormat2 = pro.cDate.ToString("yyy-MM-dd HH:mm");

            string query = "INSERT INTO " + tbn + " (industry,subIndustry,openDate,closeDate,fax,tel,web,email,employeeNum,[desc],companyName,projectName,"
            + "address) VALUES (" + pro.industry + "," + pro.subIndustry + ",'" + sqlDateFormat1 + "','" + sqlDateFormat2 + "','" + pro.fax + "','"
             + pro.tel + "','" + pro.web + "','" + pro.email + "'," + pro.employee + ",'" + pro.note + "','" + pro.cname + "','" + pro.name +"','"+pro.address+"');"
             + "SELECT project_no FROM"+tbn+"WHERE project_no = @@IDENTITY;"; 
                 

            SqlCommand cmd = new SqlCommand(query, con.connection);
            pro.ID = int.Parse(cmd.ExecuteScalar().ToString());
            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }
        }

        public void deleteProject(Project pro)
        {

            DBConnect con = new DBConnect();
            
            string tbn = "[" + con.dbName + "]." + this.tableName;
            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }
            string query = "DELETE FROM " + tbn + " WHERE id = " + pro.ID;
            SqlCommand cmd = new SqlCommand(query, con.connection);
            cmd.ExecuteNonQuery();
            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }
        }

        

        public void updateProject(Project projectUpdated)
        {
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;
            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }
            string sqlDateFormat1 = projectUpdated.oDate.ToString("yyy-MM-dd HH:mm");
            string sqlDateFormat2 = projectUpdated.cDate.ToString("yyy-MM-dd HH:mm");
            string query = string.Format("UPDATE "+tbn + " SET openDate ='{0}' ,projectName='{1}',companyName='{2}',web='{3}',address='{4}',fax='{5}',tel='{6}',employeeNum={7},[desc]='{8}',email='{9}',industry={11},subIndustry={12},closeDate='{13}'"
                + "WHERE project_no= {10}",
                sqlDateFormat1, projectUpdated.name, projectUpdated.cname, projectUpdated.web, projectUpdated.address, projectUpdated.fax, projectUpdated.tel, projectUpdated.employee
                , projectUpdated.note, projectUpdated.email, projectUpdated.ID,projectUpdated.industry,projectUpdated.subIndustry,sqlDateFormat2);
            //"UPDATE project SET note =" + "'" + projectUpdated.note + "'" + "," 
            //    + "name="+ "'"+ projectUpdated.name + "'" + "," + "date=" + "'" + projectUpdated.date + "'"
            //    + "," +"cname=" + "'" +projectUpdated.cname + "'" + "," + "web=" + "'" + projectUpdated.web
            //    + "'" + "," +"address=" + "'" + projectUpdated.address + "'" +"," +"employee=" + projectUpdated.employee
            //    +"," + "note=" + "'" + projectUpdated.note + "'" + "," + "fax=" + "'" + projectUpdated.fax + "'" +"," +
            //    "tel=" +"'" +projectUpdated.tel +"'"
            //                           + "WHERE id=" + projectUpdated.ID ;
            SqlCommand cmd = new SqlCommand(query, con.connection);
            cmd.ExecuteNonQuery();
            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }
        }
        public LinkedList<Project> allProjects()
        {
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;
            LinkedList<Project> projectList = new LinkedList<Project>();
            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }
            string query = "SELECT * FROM " + tbn;
            SqlCommand cmd = new SqlCommand(query, con.connection);
            SqlDataReader rdr = cmd.ExecuteReader();


            if (rdr.HasRows)
            {

                while (rdr.Read())
                {
                    DateTime dt1 = rdr.GetDateTime(rdr.GetOrdinal("openDate")) ; //DateTime.Parse(rdr["openDate"].ToString());
                    DateTime dt2 = rdr.GetDateTime(rdr.GetOrdinal("closeDate")); //DateTime.Parse(rdr["closeDate"].ToString());
                    Project project = new Project(int.Parse(rdr["project_no"].ToString()), dt1,dt2,
                        rdr["projectName"].ToString(),rdr["companyName"].ToString(), rdr["email"].ToString(), rdr["fax"].ToString(), rdr["tel"].ToString()
                        , rdr["address"].ToString(), rdr["web"].ToString(), int.Parse(rdr["employeeNum"].ToString()), rdr["desc"].ToString(),
                        int.Parse(rdr["industry"].ToString()),int.Parse(rdr["subIndustry"].ToString()));
                   
                    projectList.AddLast(project);


                }
            }
            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }
            return projectList;
        }

        public LinkedList<Project> allProjectsBysDep(string sortedBy, bool desc, SubDepartment sdep, SubIndustry sindust)
        {
            string sorted;
            if (sortedBy == string.Empty)
            {
                sorted = "{0}";
            }
            else
            {
                sorted = sortedBy;
            }
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;
            string tbn1 = "[" + con.dbName + "]." + this.tableName1;
            LinkedList<Project> proList = new LinkedList<Project>();
            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }
            string extra = "";
            if (desc)
            {
                extra = " DESC";
            }
            string query;
            if (sdep == null)
            {
                query = string.Format("SELECT * FROM " + tbn + "WHERE industry={0} AND subIndustry ={1} ORDER BY " + sorted + extra, sindust.industry, sindust.subIndustry);

            }
            else
            {

                if (sindust == null)
                {
                    query = string.Format("SELECT * FROM {0},{1} WHERE (department ="
                + "{1} AND subDepartment = {2} AND ProjectDepartments.project = project.project_no)" + "ORDER BY " + sorted + extra, tbn, tbn1, sdep.DepCode, sdep.SubDepCode);

                }
                else
                {

                    query = string.Format("SELECT * FROM {0},{1} WHERE (project.industry = {2} AND project.subIndustry = {3} AND department ="
                    + "{4} AND subDepartment = {5} AND ProjectDepartments.project = project.project_no)" + "ORDER BY " + sorted + extra, tbn, tbn1, sindust.industry, sindust.subIndustry,
                                    sdep.DepCode, sdep.SubDepCode);
                }
            }
            SqlCommand cmd = new SqlCommand(query, con.connection);
            SqlDataReader rdr = cmd.ExecuteReader();


            if (rdr.HasRows)
            {

                while (rdr.Read())
                {
                    DateTime dt1 = rdr.GetDateTime(rdr.GetOrdinal("openDate")); //DateTime.Parse(rdr["openDate"].ToString());
                    DateTime dt2 = rdr.GetDateTime(rdr.GetOrdinal("closeDate")); //DateTime.Parse(rdr["closeDate"].ToString());
                    Project project = new Project(int.Parse(rdr["project_no"].ToString()), dt1, dt2,
                        rdr["projectName"].ToString(), rdr["companyName"].ToString(), rdr["email"].ToString(), rdr["fax"].ToString(), rdr["tel"].ToString()
                        , rdr["address"].ToString(), rdr["web"].ToString(), int.Parse(rdr["employeeNum"].ToString()), rdr["desc"].ToString(),
                        int.Parse(rdr["industry"].ToString()), int.Parse(rdr["subIndustry"].ToString()));

                    proList.AddLast(project);


                }
            }
            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }
            return proList;
        }
    }

}
//public void updatename(Project pro, string name)
//{
//    DBConnect con = new DBConnect();
//    string tbn = "[" + con.dbName + "]." + this.tableName;
//    if (con.OpenConnection() == false)
//    {
//        throw new System.Exception("Couldn't connect to the Database");
//    }
//    string query = "UPDATE " + tbn + " SET name = '" + name
//                               + "' WHERE id = '" + pro.ID + "'";
//    SqlCommand cmd = new SqlCommand(query, con.connection);
//    cmd.ExecuteNonQuery();
//    if (con.CloseConnection() == false)
//    {
//        throw new System.Exception("Couldn't disconnect from the Database");
//    }
//}

//public void updatecName(Project pro, string cname)
//{
//    DBConnect con = new DBConnect();
//    string tbn = "[" + con.dbName + "]." + this.tableName;
//    if (con.OpenConnection() == false)
//    {
//        throw new System.Exception("Couldn't connect to the Database");
//    }
//    string query = "UPDATE " + tbn + " SET cname = '" + cname
//                               + "'WHERE id = '" + pro.ID + "'";
//    SqlCommand cmd = new SqlCommand(query, con.connection);
//    cmd.ExecuteNonQuery();
//    if (con.CloseConnection() == false)
//    {
//        throw new System.Exception("Couldn't disconnect from the Database");
//    }

//}

//public void updateDate(Project pro, DateTime date)
//{
//    DBConnect con = new DBConnect();
//    string tbn = "[" + con.dbName + "]." + this.tableName;
//    if (con.OpenConnection() == false)
//    {
//        throw new System.Exception("Couldn't connect to the Database");
//    }
//    string sqlDateFormat = pro.date.ToString("yyy-MM-dd HH:mm");
//    string query = "UPDATE " + tbn + " SET date = '" + sqlDateFormat
//                               + "'WHERE id = '" + pro.ID + "'";
//    SqlCommand cmd = new SqlCommand(query, con.connection);
//    cmd.ExecuteNonQuery();
//    if (con.CloseConnection() == false)
//    {
//        throw new System.Exception("Couldn't disconnect from the Database");
//    }

//}

//public void updateWeb(Project pro, string web)
//{
//    DBConnect con = new DBConnect();
//    string tbn = "[" + con.dbName + "]." + this.tableName;
//    if (con.OpenConnection() == false)
//    {
//        throw new System.Exception("Couldn't connect to the Database");
//    }
//    string query = "UPDATE " + tbn + " SET web = '" + web
//                               + "'WHERE id = '" + pro.ID + "'";
//    SqlCommand cmd = new SqlCommand(query, con.connection);
//    cmd.ExecuteNonQuery();
//    if (con.CloseConnection() == false)
//    {
//        throw new System.Exception("Couldn't disconnect from the Database");
//    }
//}

//public void updateEmail(Project pro, string email)
//{
//    DBConnect con = new DBConnect();
//    string tbn = "[" + con.dbName + "]." + this.tableName;
//    if (con.OpenConnection() == false)
//    {
//        throw new System.Exception("Couldn't connect to the Database");
//    }
//    string query = "UPDATE " + tbn + " SET email =" + "'" + email + "'"
//                               + "WHERE id=" + "'" + pro.ID + "'";
//    SqlCommand cmd = new SqlCommand(query, con.connection);
//    cmd.ExecuteNonQuery();
//    if (con.CloseConnection() == false)
//    {
//        throw new System.Exception("Couldn't disconnect from the Database");
//    }
//}

//public void updateFax(Project pro, string fax)
//{
//    DBConnect con = new DBConnect();
//    string tbn = "[" + con.dbName + "]." + this.tableName;
//    if (con.OpenConnection() == false)
//    {
//        throw new System.Exception("Couldn't connect to the Database");
//    }
//    string query = "UPDATE " + tbn + " SET fax =" + "'" + fax + "'"
//                               + "WHERE id=" + "'" + pro.ID + "'";
//    SqlCommand cmd = new SqlCommand(query, con.connection);
//    cmd.ExecuteNonQuery();
//    if (con.CloseConnection() == false)
//    {
//        throw new System.Exception("Couldn't disconnect from the Database");
//    }
//}

//public void updateTel(Project pro, string tel)
//{
//    DBConnect con = new DBConnect();
//    if (con.OpenConnection() == false)
//    {
//        throw new System.Exception("Couldn't connect to the Database");
//    }
//    string query = "UPDATE project SET tel =" + "'" + tel + "'"
//                               + "WHERE id=" + "'" + pro.ID + "'";
//    SqlCommand cmd = new SqlCommand(query, con.connection);
//    cmd.ExecuteNonQuery();
//    if (con.CloseConnection() == false)
//    {
//        throw new System.Exception("Couldn't disconnect from the Database");
//    }
//}

//public void updateAddress(Project pro, string address)
//{
//    DBConnect con = new DBConnect();
//    if (con.OpenConnection() == false)
//    {
//        throw new System.Exception("Couldn't connect to the Database");
//    }
//    string query = "UPDATE project SET address =" + "'" + address + "'"
//                               + "WHERE id=" + "'" + pro.ID + "'";
//    SqlCommand cmd = new SqlCommand(query, con.connection);
//    cmd.ExecuteNonQuery();
//    if (con.CloseConnection() == false)
//    {
//        throw new System.Exception("Couldn't disconnect from the Database");
//    }
//}

//public void updateEmployee(Project pro, int employee)
//{
//    DBConnect con = new DBConnect();
//    if (con.OpenConnection() == false)
//    {
//        throw new System.Exception("Couldn't connect to the Database");
//    }
//    string query = "UPDATE project SET employee =" + employee
//                               + "WHERE id=" + "'" + pro.ID + "'";
//    SqlCommand cmd = new SqlCommand(query, con.connection);
//    cmd.ExecuteNonQuery();
//    if (con.CloseConnection() == false)
//    {
//        throw new System.Exception("Couldn't disconnect from the Database");
//    }
//}

//public void updateNote(Project pro, string note)
//{
//    1
//}